/*
 *  Copyright 2005 Blandware (http://www.blandware.com)
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.blandware.atleap.persistence.hibernate.core;

import com.blandware.atleap.common.util.ObjectCountInfo;
import com.blandware.atleap.common.util.PartialCollection;
import com.blandware.atleap.common.util.QueryInfo;
import com.blandware.atleap.model.core.statistics.SearchCase;
import com.blandware.atleap.model.core.statistics.Visit;
import com.blandware.atleap.model.core.statistics.Visitor;
import com.blandware.atleap.model.core.statistics.VisitorSession;
import com.blandware.atleap.persistence.core.StatisticsDAO;
import org.hibernate.Criteria;
import org.hibernate.criterion.*;

import java.util.*;

/**
 * <p>Hibernate implementation of StatisticsDAO.</p>
 * <p><a href="StatisticsDAOHibernate.java.html"><i>View source</i></a></p>
 *
 * @author Roman Puchkovskiy <a href="mailto:roman.puchkovskiy@blandware.com">
 *         &lt;roman.puchkovskiy@blandware.com&gt;</a>
 * @version $Revision: 1.18 $ $Date: 2008/07/08 08:16:32 $
 */
public class StatisticsDAOHibernate extends BaseDAOHibernate
        implements StatisticsDAO {

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#createVisit(com.blandware.atleap.model.core.statistics.Visit)
     */
    public Long createVisit(Visit visit) {
        return (Long) getHibernateTemplate().save(visit);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#updateVisit(com.blandware.atleap.model.core.statistics.Visit)
     */
    public void updateVisit(Visit visit) {
        getHibernateTemplate().update(visit);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#getFirstFreeSessionNumber()
     */
    public Integer getFirstFreeSessionNumber() {
        String hql = new StringBuffer("select max(visit.sessionNumber) from Visit as visit ")
                .toString();
        Integer result = (Integer) findUniqueResult(hql);
        if (result == null) {
            result = new Integer(1);
        } else {
            result = new Integer(result.intValue() + 1);
        }
        return result;
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#getFirstFreeVisitorNumber()
     */
    public Integer getFirstFreeVisitorNumber() {
        String hql = new StringBuffer("select max(visit.visitorNumber) from Visit as visit ")
                .toString();
        Integer result = (Integer) findUniqueResult(hql);
        if (result == null) {
            result = new Integer(1);
        } else {
            result = new Integer(result.intValue() + 1);
        }
        return result;
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopBrowsers(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopBrowsers(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "browserId", false);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopOSes(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopOSes(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "osId", false);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopRobots(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopRobots(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "robotId", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopPages(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopPages(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "page", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopContentResources(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopContentResources(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "contentResource", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopReferrerUrls(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopReferrerUrls(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "referrerUrl", true,
                                          Restrictions.isNull("searchEngineId"));
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopReferrerDomains(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopReferrerDomains(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "referrerDomain", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopPagesEntryPoints(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopPagesEntryPoints(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "page", true,
                                          Restrictions.eq("entryPoint",
                                                          Boolean.TRUE));
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopOtherEntryPoints(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopOtherEntryPoints(QueryInfo queryInfo) {
        Criterion condition = Restrictions.conjunction()
                .add(Restrictions.eq("entryPoint", Boolean.TRUE))
                .add(Restrictions.isNull("page"));
        return listTopObjectsByExpression(queryInfo, "url", true, condition);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopPagesExitPoints(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopPagesExitPoints(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "page", true,
                                          Restrictions.eq("exitPoint",
                                                          Boolean.TRUE));
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopOtherExitPoints(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopOtherExitPoints(QueryInfo queryInfo) {
        Criterion condition = Restrictions.conjunction()
                .add(Restrictions.eq("exitPoint", Boolean.TRUE))
                .add(Restrictions.isNull("page"));
        return listTopObjectsByExpression(queryInfo, "url", true, condition);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopSearchEngines(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopSearchEngines(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "searchEngineId", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopSearchPhrases(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopSearchPhrases(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "searchPhrase", true);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopSearchWords(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopSearchWords(QueryInfo queryInfo) {
        QueryInfo queryInfoCopy = new QueryInfo(queryInfo);
        queryInfoCopy.setLimit(null);
        queryInfoCopy.setOffset(null);
        PartialCollection triads = listTopSearchPhrases(queryInfoCopy);

        return convertPhrasesToWordsRecounting(triads, queryInfo);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopInternalSearchPhrases(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopInternalSearchPhrases(QueryInfo queryInfo) {
        int limit = -1;
        int offset = -1;
        if ( queryInfo != null ) {
            if ( queryInfo.getLimit() != null ) {
                limit = queryInfo.getLimit().intValue();
            }
            if ( queryInfo.getOffset() != null ) {
                offset = queryInfo.getOffset().intValue();
            }
        }

        String expression = "query";

        Date fromDate = (Date) queryInfo.getQueryParameters().get("fromDate");
        Date tillDate = (Date) queryInfo.getQueryParameters().get("tillDate");

        Junction commonRestrictions = Restrictions.conjunction();
        commonRestrictions.add(Restrictions.between("date", fromDate, tillDate));

        List list;
        Integer total = (Integer) getSession().createCriteria(SearchCase.class)
                .setProjection(Property.forName(expression).count()
                               .setDistinct())
                .add(commonRestrictions)
                .uniqueResult();
        if (total == null) {
            total = new Integer(0);
        }

        if ( total == null || total.intValue() > 0 ) {
            Criteria criteriaForList = getSession().createCriteria(SearchCase.class)
                    .setProjection(Projections.projectionList()
                                   .add(Property.forName(expression).group())
                                   .add(Property.forName("id").count().as("total")))
                    .add(commonRestrictions)
                    .addOrder(Order.desc("total"));
            if (offset != -1) {
                criteriaForList.setFirstResult(offset);
            }
            if (limit != -1) {
                criteriaForList.setMaxResults(limit);
            }
            list = criteriaForList.list();
        } else {
            list = new ArrayList(0);
        }

        return new PartialCollection(list, total);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopInternalSearchWords(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopInternalSearchWords(QueryInfo queryInfo) {
        QueryInfo queryInfoCopy = new QueryInfo(queryInfo);
        queryInfoCopy.setLimit(null);
        queryInfoCopy.setOffset(null);
        PartialCollection triads = listTopInternalSearchPhrases(queryInfoCopy);

        return convertPhrasesToWordsRecounting(triads, queryInfo);

    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopScreenResolutions(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopScreenResolutions(QueryInfo queryInfo) {
        int limit = -1;
        int offset = -1;
        if ( queryInfo != null ) {
            if ( queryInfo.getLimit() != null ) {
                limit = queryInfo.getLimit().intValue();
            }
            if ( queryInfo.getOffset() != null ) {
                offset = queryInfo.getOffset().intValue();
            }
        }

        Date fromDate = (Date) queryInfo.getQueryParameters().get("fromDate");
        Date tillDate = (Date) queryInfo.getQueryParameters().get("tillDate");

        Junction conjunction = Restrictions.conjunction();
        conjunction.add(Restrictions.isNotNull("screenWidth"));
        conjunction.add(Restrictions.isNotNull("screenHeight"));
        Junction nullabilityCondition = Restrictions.disjunction();
        nullabilityCondition.add(conjunction);
        conjunction = Restrictions.conjunction();
        conjunction.add(Restrictions.isNull("screenWidth"));
        conjunction.add(Restrictions.isNull("screenHeight"));
        nullabilityCondition.add(conjunction);

        List list;
        // Ugly way, but I cannot find more efficient one which would work
        // everywhere
        String hqlForTotal =
                new StringBuffer("select visit.screenWidth, visit.screenHeight ")
                .append("from Visit as visit ")
                .append("where ((visit.screenWidth is not null ")
                .append("and visit.screenHeight is not null) ")
                .append("or (visit.screenWidth is null ")
                .append("and visit.screenHeight is null)) ")
                .append("and visit.date between ? and ? ")
                .append("group by visit.screenWidth, visit.screenHeight ")
                .toString();
        List numbersList = executeFind(hqlForTotal, new Object[] {fromDate,
                                                                  tillDate});
        Integer total = null;
        if (numbersList != null) {
            total = new Integer(numbersList.size());
        }
        if (total == null) {
            total = new Integer(0);
        }

        if ( total == null || total.intValue() > 0 ) {
            Criteria criteriaForList = getSession().createCriteria(Visit.class)
                    .setProjection(Projections.projectionList()
                                   .add(Property.forName("screenWidth").group())
                                   .add(Property.forName("screenHeight").group())
                                   .add(Property.forName("id").count().as("total"))
                                   .add(Property.forName("visitorNumber")
                                        .count().setDistinct().as("unique")))
                    .add(nullabilityCondition)
                    .add(Restrictions.between("date", fromDate, tillDate))
                    .addOrder(Order.desc("unique"))
                    .addOrder(Order.desc("total"));
            if (offset != -1) {
                criteriaForList.setFirstResult(offset);
            }
            if (limit != -1) {
                criteriaForList.setMaxResults(limit);
            }
            list = criteriaForList.list();
        } else {
            list = new ArrayList(0);
        }

        return new PartialCollection(list, total);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listTopColorDepths(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listTopColorDepths(QueryInfo queryInfo) {
        return listTopObjectsByExpression(queryInfo, "colorDepth", false);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listVisitors(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listVisitors(QueryInfo queryInfo) {
        String whereClause = "";
        String orderByClause = "";
        if ( queryInfo != null ) {
            whereClause = queryInfo.getWhereClause();
            orderByClause = queryInfo.getOrderByClause();
        }

        if ( whereClause == null || whereClause.length() == 0 ) {
            whereClause = "";
        }
        if ( orderByClause != null && orderByClause.length() != 0 ) {
            orderByClause = " order by " + orderByClause;
        } else {
            orderByClause = "order by visit.visitorNumber ";
        }

        List list = null;
        Integer total = null;

        String hqlPart;
        ArrayList args = new ArrayList();
        hqlPart = new StringBuffer("from Visit as visit ")
                .toString();
        if (whereClause.length() > 0) {
            hqlPart = hqlPart + "where " + whereClause;
        }

        String groupByClause = " group by visit.visitorNumber ";

        if ( queryInfo != null && queryInfo.getLimit() != null && queryInfo.getOffset() != null ) {
            // query count
            String hqlForTotal = "select count(distinct visit.visitorNumber) " + hqlPart;
            total = findUniqueIntegerResult(hqlForTotal, args.toArray());
            if ( total == null ) {
                total = new Integer(0);
            }
        }

        // If we don't have any info about the total number of results yet or
        // we know that there's something that will be found, then fetch data
        if ( total == null || total.intValue() > 0 ) {
            String hql = "";
            hql = "select visit.visitorNumber, count(distinct visit.sessionNumber), count(*), min(visit.browserId), min(visit.osId), min(visit.ip), min(visit.screenWidth), min(visit.screenHeight) " + hqlPart + groupByClause + orderByClause;
            list = executeFind(hql, queryInfo, args.toArray());
            if ( total == null ) {
                total = new Integer(list.size());
            }
        } else {
            list = new ArrayList();
        }

        List result = new ArrayList();
        for (Iterator i = list.iterator(); i.hasNext(); ) {
            Object[] row = (Object[]) i.next();
            Integer visitorNumber = (Integer) row[0];
            Integer numberOfSessions = longOrIntegerToInteger(row[1]);
            Integer numberOfVisits = longOrIntegerToInteger(row[2]);
            Integer browserId = (Integer) row[3];
            Integer osId = (Integer) row[4];
            String ip = (String) row[5];
            Integer screenWidth = (Integer) row[6];
            Integer screenHeight = (Integer) row[7];

            Visitor visitor = new Visitor();
            visitor.setNumber(visitorNumber);
            visitor.setNumberOfSessions(numberOfSessions);
            visitor.setNumberOfVisits(numberOfVisits);
            visitor.setBrowserId(browserId);
            visitor.setOsId(osId);
            visitor.setIp(ip);
            visitor.setScreenWidth(screenWidth);
            visitor.setScreenHeight(screenHeight);

            result.add(visitor);
        }

        return new PartialCollection(result, total);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listVisitorSessions(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listVisitorSessions(QueryInfo queryInfo) {
        String whereClause = "";
        String orderByClause = "";
        if ( queryInfo != null ) {
            whereClause = queryInfo.getWhereClause();
            orderByClause = queryInfo.getOrderByClause();
        }

        if ( whereClause == null || whereClause.length() == 0 ) {
            whereClause = "";
        }
        if ( orderByClause != null && orderByClause.length() != 0 ) {
            orderByClause = " order by " + orderByClause;
        } else {
            orderByClause = "order by visit.sessionNumber ";
        }

        Integer visitorNumber = null;
        if (queryInfo != null) {
            Map parameters = queryInfo.getQueryParameters();
            if (parameters != null) {
                visitorNumber = Integer.valueOf((String) parameters.get("visitorNumber"));
            }
        }

        List list = null;
        Integer total = null;

        String hqlPart;
        ArrayList args = new ArrayList();
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where visit.visitorNumber = ? ")
                .toString();
        args.add(visitorNumber);
        if (whereClause.length() > 0) {
            hqlPart = hqlPart + "and " + whereClause;
        }

        String groupByClause = " group by visit.sessionNumber ";

        if ( queryInfo != null && queryInfo.getLimit() != null && queryInfo.getOffset() != null ) {
            // query count
            String hqlForTotal = "select count(distinct visit.sessionNumber) " + hqlPart;
            total = findUniqueIntegerResult(hqlForTotal, args.toArray());
            if ( total == null ) {
                total = new Integer(0);
            }
        }

        // If we don't have any info about the total number of results yet or
        // we know that there's something that will be found, then fetch data
        if ( total == null || total.intValue() > 0 ) {
            String hql;
            hql = "select visit.sessionNumber, count(distinct visit.id), min(visit.date), max(visit.date) " + hqlPart + groupByClause + orderByClause;
            list = executeFind(hql, queryInfo, args.toArray());
            if ( total == null ) {
                total = new Integer(list.size());
            }
        } else {
            list = new ArrayList();
        }

        List result = new ArrayList();
        for (Iterator i = list.iterator(); i.hasNext(); ) {
            Object[] row = (Object[]) i.next();
            Integer sessionNumber = (Integer) row[0];
            Integer numberOfVisits = longOrIntegerToInteger(row[1]);
            Date beginDate = (Date) row[2];
            Date endDate = (Date) row[3];

            VisitorSession visitorSession = new VisitorSession();
            visitorSession.setNumber(sessionNumber);
            visitorSession.setNumberOfVisits(numberOfVisits);
            visitorSession.setBeginDate(beginDate);
            visitorSession.setEndDate(endDate);

            result.add(visitorSession);
        }

        return new PartialCollection(result, total);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listVisits(com.blandware.atleap.common.util.QueryInfo)
     */
    public PartialCollection listVisits(QueryInfo queryInfo) {
        String whereClause = "";
        String orderByClause = "";
        if ( queryInfo != null ) {
            whereClause = queryInfo.getWhereClause();
            orderByClause = queryInfo.getOrderByClause();
        }

        if ( whereClause == null || whereClause.length() == 0 ) {
            whereClause = "";
        }
        if ( orderByClause != null && orderByClause.length() != 0 ) {
            orderByClause = " order by " + orderByClause;
        } else {
            orderByClause = "order by visit.date ";
        }

        Integer sessionNumber = null;
        if (queryInfo != null) {
            Map parameters = queryInfo.getQueryParameters();
            if (parameters != null) {
                sessionNumber = Integer.valueOf((String) parameters.get("sessionNumber"));
            }
        }

        List list;
        Integer total = null;

        String hqlPart;
        ArrayList args = new ArrayList();
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where visit.sessionNumber = ? ")
                .toString();
        args.add(sessionNumber);
        if (whereClause.length() > 0) {
            hqlPart = hqlPart + "and " + whereClause;
        }

        if ( queryInfo != null && queryInfo.getLimit() != null && queryInfo.getOffset() != null ) {
            // query count
            String hqlForTotal = "select count(distinct visit.id) " + hqlPart;
            total = findUniqueIntegerResult(hqlForTotal, args.toArray());
            if ( total == null ) {
                total = new Integer(0);
            }
        }

        // If we don't have any info about the total number of results yet or
        // we know that there's something that will be found, then fetch data
        if ( total == null || total.intValue() > 0 ) {
            String hql;
            hql = "select visit " + hqlPart + orderByClause;
            list = executeFind(hql, queryInfo, args.toArray());
            if ( total == null ) {
                total = new Integer(list.size());
            }
        } else {
            list = new ArrayList();
        }

        return new PartialCollection(list, total);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#getTotalsMonthly()
     */
    public List getTotalsMonthly() {
        List list = new ArrayList();

        Date firstVisitDate = (Date) findUniqueResult("select min(visit.date) from Visit visit ");
        Date lastVisitDate = (Date) findUniqueResult("select max(visit.date) from Visit visit ");
        if (firstVisitDate != null && lastVisitDate != null) {
            Calendar firstVisitCalendar = Calendar.getInstance();
            Calendar lastVisitCalendar = Calendar.getInstance();
            firstVisitCalendar.setTime(firstVisitDate);
            lastVisitCalendar.setTime(lastVisitDate);

            Calendar currentMonth = Calendar.getInstance();
            currentMonth.clear();
            currentMonth.set(firstVisitCalendar.get(Calendar.YEAR),
                             firstVisitCalendar.get(Calendar.MONTH), 1);

            Calendar nextMonth = (Calendar) currentMonth.clone();
            nextMonth.add(Calendar.MONTH, 1);

            String hqlPart;
            hqlPart = new StringBuffer("from Visit as visit ")
                    .append("where visit.date >= ? ")
                    .append("and visit.date < ? ")
                    .toString();
            String hql = "select count(*), count(distinct visit.visitorNumber) " + hqlPart;

            while (currentMonth.before(lastVisitCalendar)) {
                ArrayList args = new ArrayList();
                args.add(currentMonth.getTime());
                args.add(nextMonth.getTime());
                Object[] row = (Object[]) findUniqueResult(hql, args.toArray());
                if (row != null) {
                    Integer total = longOrIntegerToInteger(row[0]);
                    Integer unique = longOrIntegerToInteger(row[1]);
                    if (total.intValue() > 0) {
                        Integer year = new Integer(currentMonth.get(Calendar.YEAR));
                        Integer month = new Integer(currentMonth.get(Calendar.MONTH) + 1);
                        list.add(new Object[]{total, unique, year, month});
                    }
                }

                currentMonth.add(Calendar.MONTH, 1);
                nextMonth.add(Calendar.MONTH, 1);
            }
        }

        List result = new ArrayList();
        if (list != null && !list.isEmpty()) {
            Integer lastYear = null;

            List curYearList = null;
            for (Iterator i = list.iterator(); i.hasNext(); ) {
                Object[] objects = (Object[]) i.next();
                Integer total = (Integer) objects[0];
                Integer unique = (Integer) objects[1];
                Integer year = (Integer) objects[2];
                Integer month = (Integer) objects[3];
                if (lastYear == null || !lastYear.equals(year)) {
                    if (curYearList != null) {
                        result.add(new Object[]{lastYear, curYearList});
                    }
                    curYearList = new ArrayList();
                }
                lastYear = year;
                Object[] monthDescr = new Object[]{month, total, unique};
                curYearList.add(monthDescr);
            }
            result.add(new Object[]{lastYear, curYearList});
        }

        return result;
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#getTotalsDaily(Integer,Integer)
     */
    public List getTotalsDaily(Integer year, Integer month) {
/*
        String hqlPart = new String();
        ArrayList args = new ArrayList();
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where year(visit.date) = ? ")
                .append("and month(visit.date) = ? ")
                .append("group by day(visit.date) ")
                .append("order by day(visit.date) ")
                .toString();
        args.add(year);
        args.add(month);
        String hql = "select day(visit.date), count(*), count(distinct visit.visitorNumber) " + hqlPart;

        List list = executeFind(hql, args.toArray());

        return list;
*/
        List result = new ArrayList();

        Calendar currentDay = Calendar.getInstance();
        currentDay.clear();
        currentDay.set(year.intValue(), month.intValue() - 1, 1);

        Calendar nextDay = (Calendar) currentDay.clone();
        nextDay.add(Calendar.DAY_OF_MONTH, 1);

        String hqlPart;
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where visit.date >= ? ")
                .append("and visit.date < ? ")
                .toString();
        String hql = "select count(*), count(distinct visit.visitorNumber) " + hqlPart;

        int currentMonth = currentDay.get(Calendar.MONTH);
        while (currentDay.get(Calendar.MONTH) == currentMonth) {
            ArrayList args = new ArrayList();
            args.add(currentDay.getTime());
            args.add(nextDay.getTime());
            Object[] row = (Object[]) findUniqueResult(hql, args.toArray());
            if (row != null) {
                Integer total = longOrIntegerToInteger(row[0]);
                Integer unique = longOrIntegerToInteger(row[1]);
                if (total.intValue() > 0) {
                    Integer numberOfDay = new Integer(currentDay.get(Calendar.DAY_OF_MONTH));
                    result.add(new Object[]{numberOfDay, total, unique});
                }
            }

            currentDay.add(Calendar.DAY_OF_MONTH, 1);
            nextDay.add(Calendar.DAY_OF_MONTH, 1);
        }

        return result;
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#getTotalsHourly(Integer, Integer, Integer)
     */
    public List getTotalsHourly(Integer year, Integer month, Integer day) {
/*
        String hqlPart = new String();
        ArrayList args = new ArrayList();
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where year(visit.date) = ? ")
                .append("and month(visit.date) = ? ")
                .append("and day(visit.date) = ? ")
                .append("group by hour(visit.date) ")
                .append("order by hour(visit.date) ")
                .toString();
        args.add(year);
        args.add(month);
        args.add(day);
        String hql = "select hour(visit.date), count(*), count(distinct visit.visitorNumber) " + hqlPart;

        List list = executeFind(hql, args.toArray());

        return list;
*/
        List result = new ArrayList();

        Calendar currentHour = Calendar.getInstance();
        currentHour.clear();
        currentHour.set(year.intValue(), month.intValue() - 1, day.intValue());
        Calendar nextHour = (Calendar) currentHour.clone();
        nextHour.add(Calendar.HOUR_OF_DAY, 1);

        String hqlPart;
        hqlPart = new StringBuffer("from Visit as visit ")
                .append("where visit.date >= ? ")
                .append("and visit.date < ? ")
                .toString();
        String hql = "select count(*), count(distinct visit.visitorNumber) " + hqlPart;
        for (int i = 0; i < 24; i++) {
            ArrayList args = new ArrayList();
            args.add(currentHour.getTime());
            args.add(nextHour.getTime());
            Object[] row = (Object[]) findUniqueResult(hql, args.toArray());
            if (row != null) {
                Integer total = longOrIntegerToInteger(row[0]);
                Integer unique = longOrIntegerToInteger(row[1]);
                if (total.intValue() > 0) {
                    result.add(new Object[]{new Integer(i), total, unique});
                }
            }

            currentHour.add(Calendar.HOUR_OF_DAY, 1);
            nextHour.add(Calendar.HOUR_OF_DAY, 1);
        }

        return result;
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listUserVisits(String)
     */
    public List listUserVisits(String username) {
        return listVisitsOn1ArgCondition("visit.user is not null and visit.user.name = ?", username);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listPageVisits(Long)
     */
    public List listPageVisits(Long pageId) {
        return listVisitsOn1ArgCondition("visit.page is not null and visit.page.id = ?", pageId);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#listContentResourceVisits(Long)
     */
    public List listContentResourceVisits(Long contentResourceId) {
        return listVisitsOn1ArgCondition("visit.contentResource is not null and visit.contentResource.id = ?", contentResourceId);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#retrieveVisit(Long)
     */
    public Visit retrieveVisit(Long visitId) {
        return (Visit) getHibernateTemplate().get(Visit.class, visitId);
    }

    /**
     * @see com.blandware.atleap.persistence.core.StatisticsDAO#createSearchCase(com.blandware.atleap.model.core.statistics.SearchCase)
     */
    public Long createSearchCase(SearchCase searchCase) {
        return (Long) getHibernateTemplate().save(searchCase);
    }

    /**
     * Lists visits which satisfy to some WHERE condition, which contains
     * exactly one argument placeholder. Visit is aliased as 'visit'.
     *
     * @param where WHERE clause
     * @param arg argument
     * @return list of results
     */
    protected List listVisitsOn1ArgCondition(String where, Object arg) {
        String hql = "select visit from Visit as visit where " + where;
        ArrayList args = new ArrayList();
        args.add(arg);
        return executeFind(hql, args.toArray());
    }

    /**
     * Lists most popular of some objects; grouping is determined by an HQL
     * expression.
     *
     * @param queryInfo query info
     * @param expression HQL expression that determines object
     * @param forceNotNull if true, condition will be appended to WHERE clause
     * that will ensure that object defined by expression is not NULL.
     * @return partial collection consisting of arrays of two elements: first
     * of them is object, second is count ('popularity')
     */
    protected PartialCollection listTopObjectsByExpression(QueryInfo queryInfo,
                                                           String expression,
                                                           boolean forceNotNull) {
        return listTopObjectsByExpression(queryInfo, expression, forceNotNull,
                                          null);
    }

    /**
     * Lists most popular of some objects; grouping is determined by an HQL
     * expression.
     *
     * @param queryInfo query info
     * @param expression HQL expression that determines object
     * @param forceNotNull if true, condition will be appended to WHERE clause
     * that will ensure that object defined by expression is not NULL.
     * @param condition condition that will be applied
     * @return partial collection consisting of arrays of three elements: first
     * of them is object, second and third are is counts ('popularity')
     */
    protected PartialCollection listTopObjectsByExpression(QueryInfo queryInfo,
                                                           String expression,
                                                           boolean forceNotNull,
                                                           Criterion condition) {
        int limit = -1;
        int offset = -1;
        if ( queryInfo != null ) {
            if ( queryInfo.getLimit() != null ) {
                limit = queryInfo.getLimit().intValue();
            }
            if ( queryInfo.getOffset() != null ) {
                offset = queryInfo.getOffset().intValue();
            }
        }

        Date fromDate = (Date) queryInfo.getQueryParameters().get("fromDate");
        Date tillDate = (Date) queryInfo.getQueryParameters().get("tillDate");

        Junction commonRestrictions = Restrictions.conjunction();
        if (forceNotNull) {
            commonRestrictions.add(Restrictions.isNotNull(expression));
        }
        if (condition != null) {
            commonRestrictions.add(condition);
        }
        commonRestrictions.add(Restrictions.between("date", fromDate, tillDate));

        List list;
        Integer total = (Integer) getSession().createCriteria(Visit.class)
                .setProjection(Property.forName(expression).count()
                               .setDistinct())
                .add(commonRestrictions)
                .uniqueResult();
        if (total == null) {
            total = new Integer(0);
        }

        if ( total == null || total.intValue() > 0 ) {
            Criteria criteriaForList = getSession().createCriteria(Visit.class)
                    .setProjection(Projections.projectionList()
                                   .add(Property.forName(expression).group())
                                   .add(Property.forName("id").count().as("total"))
                                   .add(Property.forName("visitorNumber")
                                        .count().setDistinct().as("unique")))
                    .add(commonRestrictions)
                    .addOrder(Order.desc("unique"))
                    .addOrder(Order.desc("total"));
            if (offset != -1) {
                criteriaForList.setFirstResult(offset);
            }
            if (limit != -1) {
                criteriaForList.setMaxResults(limit);
            }
            list = criteriaForList.list();
        } else {
            list = new ArrayList(0);
        }

        return new PartialCollection(list, total);
    }

    /**
     * Takes a list and returns a portion of it, interpreting limit and offset
     * in queryInfo. Result is returned as a PartialCollection.
     *
     * @param list list which portion will be packed into a PartialCollection
     * @param total total number of elements in initial list
     * @param queryInfo contains limit and offset info
     * @return a partial collection
     */
    protected PartialCollection makePartialCollectionTruncating(List list,
                                                                Integer total,
                                                                QueryInfo queryInfo) {
        List sublist = makeListTruncating(list, queryInfo);

        return new PartialCollection(sublist, total);
    }

    /**
     * Takes a list and returns a portion of it, interpreting limit and offset
     * in queryInfo. Result is returned as a List.
     *
     * @param list list which portion will be returned
     * @param queryInfo contains limit and offset info
     * @return a list
     */
    protected List makeListTruncating(List list, QueryInfo queryInfo) {
        Integer limit = null;
        Integer offset = null;
        if (queryInfo != null) {
            limit = queryInfo.getLimit();
            offset = queryInfo.getOffset();
        }

        int listSize = list.size();
        List sublist;
        if (limit == null) {
            // no limit, whole list will be represented in result
            sublist = list;
        } else {
            if (offset == null) {
                // no offset means offset from 0
                offset = new Integer(0);
            }
            int offsetValue = offset.intValue();
            int limitValue = limit.intValue();
            if (offsetValue >= listSize) {
                // offset is beyond the bounds of list, result is empty
                // collection
                sublist = new ArrayList();
            } else {
                if (offsetValue + limitValue > listSize) {
                    // there're no enough elements, reduce limit
                    limitValue = listSize - offsetValue;
                }
                sublist = new ArrayList(list.subList(offsetValue,
                                                     offsetValue + limitValue));
            }
        }
        return sublist;
    }

    /**
     * Breakes a phrase into words by spacing symbols.
     *
     * @param phrase phrase to split
     * @return list of words
     */
    protected List getWordsFromPhrase(String phrase) {
        List words = new ArrayList();
        boolean lastCharIsWhitespace = true;
        int wordBegin = 0;
        for (int i = 0; i < phrase.length(); i++) {
            char ch = phrase.charAt(i);
            if (Character.isWhitespace(ch)) {
                if (lastCharIsWhitespace) {
                    // skip it
                } else {
                    // word has ended
                    String word = phrase.substring(wordBegin, i);
                    words.add(word);
                }
                lastCharIsWhitespace = true;
            } else {
                if (lastCharIsWhitespace) {
                    // word is beginning
                    wordBegin = i;
                } else {
                    // still in word
                }
                lastCharIsWhitespace = false;
            }
        }
        if (!lastCharIsWhitespace) {
            // last word has not yet been extracted
            String word = phrase.substring(wordBegin);
            words.add(word);
        }
        return words;
    }

    /**
     * Takes a list of phrases with count info, breakes phrases to words and
     * updates counts.
     *
     * @param triads array of triads: phrase, total count, unique count (last
     * element is not required)
     * @param queryInfo query info
     * @return list of ObjectCountInfo instances
     */
    protected PartialCollection convertPhrasesToWordsRecounting(
            PartialCollection triads, QueryInfo queryInfo) {
        Map wordsToInfo = new HashMap();
        for (Iterator i = triads.iterator(); i.hasNext(); ) {
            Object[] triad = (Object[]) i.next();
            String searchPhrase = (String) triad[0];
            Integer total = (Integer) triad[1];
            Integer unique;
            if (triad.length > 2) {
                unique = (Integer) triad[2];
            } else {
                unique = new Integer(0);
            }
            List words = getWordsFromPhrase(searchPhrase);
            for (Iterator j = words.iterator(); j.hasNext(); ) {
                String word = (String) j.next();
                ObjectCountInfo info = (ObjectCountInfo) wordsToInfo.get(word);
                if (info == null) {
                    wordsToInfo.put(word, new ObjectCountInfo(word, total,
                                                              unique));
                } else {
                    info.increaseCounts(total, unique);
                }
            }
        }

        List result = new ArrayList(wordsToInfo.size());
        for (Iterator i = wordsToInfo.keySet().iterator(); i.hasNext(); ) {
            Object key = i.next();
            ObjectCountInfo info = (ObjectCountInfo) wordsToInfo.get(key);
            result.add(info);
        }
        Collections.sort(result);

        return makePartialCollectionTruncating(result,
                                               new Integer(result.size()),
                                               queryInfo);
    }
}
